﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace PublSystem
{
    public partial class Edit : System.Web.UI.Page
    {
        int pageId;
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                pageId = int.Parse(Request.QueryString["id"]);
            }
            catch
            {
                pageId = 1;
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM PublicationsInfo.Publications WHERE publicationId = " + pageId + ";", conn);
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                rdr.Read();
                if (!IsPostBack)
                {
                    txtTitle.Text = rdr["title"].ToString();
                    txtContent.Text = rdr["summary"].ToString();
                }


                rdr.Close();
                SqlCommand cmdKw = new SqlCommand(@"SELECT * FROM PublicationsInfo.Keywords
                                                        , PublicationsInfo.KeywordsPublications , PublicationsInfo.Publications WHERE PublicationsInfo.KeywordsPublications.keywordId = PublicationsInfo.Keywords.keywordId 
                                                        AND PublicationsInfo.Publications.publicationId = PublicationsInfo.KeywordsPublications.publicationId
                                                        AND PublicationsInfo.Publications.publicationId=" + pageId, conn);

                SqlDataReader rdrkw = cmdKw.ExecuteReader();
                if (!IsPostBack)
                {
                    while (rdrkw.Read())
                    {
                        txtKeywords.Text = txtKeywords.Text + rdrkw["keyword"] + " ";
                    }
                }
                rdrkw.Close();
            }
        }

        protected void ButbuttonSubmit_Click(object sender, EventArgs e)
        {
            int kwId = 1;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
            {
                SqlCommand cmd = new SqlCommand("UPDATE PublicationsInfo.Publications SET summary = @param WHERE publicationId=" + pageId, conn);
                SqlParameter paramTitle = new SqlParameter();
                paramTitle.ParameterName = "@param";
                paramTitle.Value = txtContent.Text;
                cmd.Parameters.Add(paramTitle);
                conn.Open();
                cmd.ExecuteScalar();
                SqlCommand cmdUpdTitle = new SqlCommand("UPDATE PublicationsInfo.Publications SET title = @param WHERE publicationId=" + pageId, conn);
                SqlParameter paramSummary = new SqlParameter();
                paramSummary.ParameterName = "@param";
                paramSummary.Value = txtTitle.Text;
                cmdUpdTitle.Parameters.Add(paramSummary);
                cmdUpdTitle.ExecuteScalar();

                SqlCommand cmdDeleteKeywordsLinks =
                        new SqlCommand(
                            "DELETE FROM PublicationsInfo.KeywordsPublications WHERE publicationId=" + pageId,
                            conn);
                cmdDeleteKeywordsLinks.ExecuteScalar();

                var pattern = new Regex(
                    @"( [^\W_\d]              # starting with a letter
                            # followed by a run of either...
      ( [^\W_\d] |          #   more letters or
        [-'\d](?=[^\W_\d])  #   ', -, or digit followed by a letter
      )*
      [^\W_\d]              # and finishing with a letter
    )",
                    RegexOptions.IgnorePatternWhitespace);

                var input = txtKeywords.Text;
                List<string> listOfKeywords = new List<string>();

                foreach (Match m in pattern.Matches(input))
                    listOfKeywords.Add(m.Groups[1].Value);

                string[] splitedText = listOfKeywords.ToArray();

                for (int k = 0; k < splitedText.Length; k++)
                {
                    if (splitedText[k].Trim() == " ") continue;
                    string executor = @"IF EXISTS (SELECT keywordId FROM PublicationsInfo.Keywords WHERE keyword=@kw)
                          BEGIN 
                            SELECT keywordId FROM PublicationsInfo.Keywords WHERE keyword=@kw;
                          END 
                        ELSE
                          BEGIN
                            INSERT INTO PublicationsInfo.Keywords(keyword) OUTPUT INSERTED.keywordId VALUES(@kw);
                          END";

                    using (SqlCommand cmdd = new SqlCommand(executor, conn))
                    {
                        SqlParameter kwpar = new SqlParameter();
                        kwpar.ParameterName = "@kw";
                        kwpar.Value = splitedText[k];
                        cmdd.Parameters.Add(kwpar);
                        try
                        {
                            kwId = (int)cmdd.ExecuteScalar();
                        }
                        catch
                        {
                            continue;
                        }
                    }

                    using (
                        SqlCommand cmdd =
                            new SqlCommand(
                                string.Format(
                                    "INSERT INTO PublicationsInfo.KeywordsPublications(keywordId, publicationId) VALUES ({0}, {1})",
                                    kwId, pageId)))
                    {
                        cmdd.Connection = conn;
                        try
                        {
                            cmdd.ExecuteScalar();
                        }
                        catch
                        {
                            continue;
                        }
                    }

                   
                }
            }
             Response.Redirect("Publication?id=" + pageId);
        }
    }
}